The primary problem in this case is not just the many-to-many relationship, but the fact that it's a self-referential, many-to-many relationship. Because automap is simply translating the mapped class names to relationship names, it constructs the same name, e.g. task_collection, for both directions of the relationship, and the naming collision generates the error. This shortcoming of automap feels significant in that self-referential, many-to-many relationships are not uncommon.

Explicitly adding the relationships you want, using your own names, won't solve the problem because automap will still try to create the task_collection relationships. To deal with this issue, we need to override task_collection.

If you're okay with keeping the name task_collection for the forward direction of the relationship, we can simply pre-define the relationship--specifying whatever name we want for the backref. If automap finds the expected property already in place, it will assume the relationship is being overridden and not try to add it.

Here's a stripped down example, along with the an sqlite database for testing.

Putting it into a file called setup_self.sql, we can do:

If you want to have a name other than task_collection, you need to use automap's function for overriding collection-relationship names:

The arguments local_cls and referred_cls are instances of the mapped table classes. For a self-referential, many-to-many relationship, these are both the same class. We can use the arguments to build a key that allows us to identify overrides.

Here is an example implementation of this approach.

Note that the overriding of name_for_collection_relationship simply changes the name that automap uses for the relationship. In our case, the relationship is still being pre-defined by Task. But, the override tells automap to look for forward instead of task_collection, which it finds and therefore discontinues defining the relationship.

Under some circumstances, it would be nice if we could override the relationship names without having to pre-define the actual relationship. On first consideration, this should be possible using name_for_collection_relationship. However, I could not get this approach to work for self-referential, many-to-many relationships, due to a combination of two reasons.

name_for_collection_relationship and the related generate_relationship are called twice, once for each direction of the many-to-many relationship. In both cases, local_cls and referred_cls are the same, because of the self-referentiality. Moreover, the other arguments of name_for_collection_relationship are effectively equivalent. Therefore, we cannot, from the context of the function call, determine which direction we are overriding.

Here is the even-more surprising part of the problem. It appears we cannot even count on one direction happening before the other. In other words, the two calls to name_for_collection_relationship and generate_relationship are very similar. The argument that actually determines the directionality of the relationship is constraint, which is one of the two foreign-key constraints for the relationship; these constraints are loaded, from Base.metadata, into a variable called m2m_const. Herein lies the problem. The order that the constraints end up in m2m_const is nondeterministic, i.e. sometimes it will be one order; other times it will be the opposite (at least when using sqlite3). Because of this, the directionality of the relationship is nondeterministic.

On the other hand, when we pre-define the relationship, the following arguments create the necessary determinism.

Of particular note, I actually tried to create a solution that simply overrode the relationship names without pre-defining it. It exhibited the described nondeterminism.

If you have a reasonable number of database tables that do not change often, I would suggest just using Declarative Base. It might be a little more work to set up, but it gives you more control.